/*
 * Copyright (c) 2012 CCXE(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCXE(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCXE(China) Co.,Ltd.
 *
 * Author: zhaoshujin
 * Date: 2012-3-6 下午01:35:24
 */
package com.jxl.excelplugin;

import java.io.File;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 统一财报模板指标编码 使用重新排列后的指标编码，更新到旧版财报模板中
 * 
 * @author zhaoshujin
 * 
 */
public class UnifyReportItem {

	public static String sourceFile = "F:\\MYDOCUMENT\\接收的文件\\l刘夜明\\新准则-资产负债表_20120229.xls";
	public static String targetFile = "F:\\MYDOCUMENT\\ExcelPlugin\\文档\\落地数据准备\\指标函数整理.xls";

	public void unifyItem() {

		try {

			Workbook wb = Workbook.getWorkbook(new File(sourceFile));

			Workbook wbt = Workbook.getWorkbook(new File(targetFile));
			WritableWorkbook wwb = Workbook.createWorkbook(new File(targetFile), wbt);

			Sheet st = wb.getSheet("对照表-新准则-资产负债表");
			WritableSheet ws = wwb.getSheet("新资产负债表");

			unify(st, ws);

			wwb.write();
			wwb.close();
			wbt.close();
			wb.close();

		} catch (Exception e) {

			e.printStackTrace();
		}

	}

	public void unify(Sheet st, WritableSheet ws) {

		String oldName, oldItem, newName, newItem = null;

		int rows = st.getRows();

		try {
			// 读取“新”的财报模板指标编码
			String[][] newArray = new String[rows - 1][2];
			for (int r = 0; r < rows - 1; r++) {
				newArray[r][0] = st.getCell(0, r + 1).getContents().trim();
				newArray[r][1] = st.getCell(1, r + 1).getContents();
			}
			
			// 读取“旧”财报模板指标编码
			rows = ws.getRows();
			String[][] oldArray = new String[rows - 1][2];
			for (int r = 0; r < rows - 1; r++) {
				oldArray[r][0] = ws.getCell(0, r).getContents().trim();
				oldArray[r][1] = ws.getCell(1, r).getContents();
			}
			
			//比较新-旧财报模板指标对应关系，替换为新指标编码
			for (int r = 0; r < oldArray.length; r++) {
				oldName = oldArray[r][0];
				oldItem = oldArray[r][1];

				for (int rr = 0; rr < newArray.length; rr++) {
					newName = newArray[rr][0];
					newItem = newArray[rr][1];

					if (oldName.equals(newName)) {
						oldItem = newItem;
						ws.addCell(new jxl.write.Number(5, r, Integer.parseInt(oldItem)));
						break;
					}
				}

			}
			
		} catch (Exception e) {

			e.printStackTrace();
		}

	}

	public static void main(String[] args) {

		UnifyReportItem uri = new UnifyReportItem();
		long start = System.currentTimeMillis();

		uri.unifyItem();

		long end = System.currentTimeMillis();
		System.out.println((end - start) + " ms");
	}
}
